package deweb.cimaplanning.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.cs.common.pool.DBDAO;
import com.cs.common.pool.exception.PoolException;

import deweb.cimaplanning.misc;
import deweb.cimaplanning.dto.CostsPlants;

public class CostsPlantsDao extends DBDAO {
	/** Datasource name */
	private static final String dsName = misc.DS_NAME;//$NON-NLS-1$
	
	/**
	 * Obtiene informacion para el reporte de costos, en especifico por tipo de maíz 
	 *
	 * @param versionId
	 * @param plantId
	 * @param cornTypeId
	 * @return array
	 * @throws Exception
	 */
	public ArrayList getArrayForCostsReportsByCornType(int versionId, int plantId, int cornTypeId) throws Exception {
		String sql				= null;
		CostsPlants dto			= null;
		ArrayList array			= null;
		PreparedStatement pstm	= null;
		ResultSet rs			= null;
		
		try{
			array = new ArrayList(100);
			
			sql  = "SELECT cp.plant_version_id, cp.corn_type_id, cp.corn_origin_id,  ";
			sql += "		cp.initial_cost, cp.initial_tons, cp.initial_usd,  ";
			sql += "		cp.incoming_cost, cp.incoming_tons, cp.incoming_usd,  ";
			sql += "		cp.available_cost, cp.available_tons, cp.available_usd,  ";
			sql += "		cp.grind_cost, cp.grind_tons, cp.grind_usd,  ";
			sql += "		cp.final_cost, cp.final_tons, cp.final_usd ";
			sql += "	FROM vw_costs_plants_corns_types_totals cp, vw_v_plants_versions pv ";
			sql += "	WHERE cp.plant_version_id = pv.plant_version_id ";
			sql += "		AND pv.version_id = " + PREPARED_STATEMENT_PARAMETER_TOKEN;
			sql += "		AND pv.plant_id = " + PREPARED_STATEMENT_PARAMETER_TOKEN;
			sql += "		AND cp.corn_type_id = " + PREPARED_STATEMENT_PARAMETER_TOKEN;
			sql += "	ORDER BY cp.plant_version_id, cp.corn_type_id ";

			pstm = prepareSQL(sql, dsName);
			
			pstm.setInt(1, versionId);
			pstm.setInt(2, plantId);
			pstm.setInt(3, cornTypeId);
			
			rs = executeQuery(pstm);
			
			while (rs.next()){
				dto = new CostsPlants();
				
				dto.setPlantVersionId(rs.getInt("plant_version_id"));
				dto.setCornTypeId(rs.getInt("corn_type_id"));
				dto.setCornOriginId(rs.getString("corn_origin_id"));
				dto.setInitialCost(rs.getDouble("initial_cost"));
				dto.setInitialTons(rs.getDouble("initial_tons"));
				dto.setInitialUsd(rs.getDouble("initial_usd"));
				dto.setIncomingCost(rs.getDouble("incoming_cost"));
				dto.setIncomingTons(rs.getDouble("incoming_tons"));
				dto.setIncomingUsd(rs.getDouble("incoming_usd"));
				dto.setAvailableCost(rs.getDouble("available_cost"));
				dto.setAvailableTons(rs.getDouble("available_tons"));
				dto.setAvailableUsd(rs.getDouble("available_usd"));
				dto.setGrindCost(rs.getDouble("grind_cost"));
				dto.setGrindTons(rs.getDouble("grind_tons"));
				dto.setGrindUsd(rs.getDouble("grind_usd"));
				dto.setFinalCost(rs.getDouble("final_cost"));
				dto.setFinalTons(rs.getDouble("final_tons"));
				dto.setFinalUsd(rs.getDouble("final_usd"));
				
				array.add(dto);
			}
			
			array.trimToSize();
			
		}catch (PoolException poolException){
			Exception e = null;
			poolException.printStackTrace(System.out);
			e = new Exception(poolException);
			throw e;
		} catch(SQLException sqle){
			Exception e = null;
			sqle.printStackTrace(System.out);
			e = new Exception(sqle);
			throw e;
		}finally {
			try{ closeConnection(pstm.getConnection());	}catch(Exception exception){}
		}
		
		return array;
	}
	
	/**
	 * Obtiene informacion para el reporte de costos de perdidas de futuros, en especifico por tipo de maíz 
	 *
	 * @param versionId
	 * @param plantId
	 * @param cornTypeId
	 * @return array
	 * @throws Exception
	 */
	public ArrayList getArrayForCostsReportsByCornTypeFutLoss(int versionId, int plantId, int cornTypeId) throws Exception {
		String sql				= null;
		CostsPlants dto			= null;
		ArrayList array			= null;
		PreparedStatement pstm	= null;
		ResultSet rs			= null;
		
		try{
			array = new ArrayList(100);
			
			sql  = "SELECT cp.plant_version_id, cp.corn_type_id, cp.corn_origin_id,  ";
			sql += "		cp.initial_cost, cp.initial_tons, cp.initial_usd,  ";
			sql += "		cp.incoming_cost, cp.incoming_tons, cp.incoming_usd,  ";
			sql += "		cp.available_cost, cp.available_tons, cp.available_usd,  ";
			sql += "		cp.grind_cost, cp.grind_tons, cp.grind_usd,  ";
			sql += "		cp.final_cost, cp.final_tons, cp.final_usd ";
			sql += "	FROM vw_costs_plants_corns_types_totals_FUT_P cp, vw_v_plants_versions pv ";
			sql += "	WHERE cp.plant_version_id = pv.plant_version_id ";
			sql += "		AND pv.version_id = " + PREPARED_STATEMENT_PARAMETER_TOKEN;
			sql += "		AND pv.plant_id = " + PREPARED_STATEMENT_PARAMETER_TOKEN;
			sql += "		AND cp.corn_type_id = " + PREPARED_STATEMENT_PARAMETER_TOKEN;
			sql += "	ORDER BY cp.plant_version_id, cp.corn_type_id ";

			pstm = prepareSQL(sql, dsName);
			
			pstm.setInt(1, versionId);
			pstm.setInt(2, plantId);
			pstm.setInt(3, cornTypeId);
			
			System.out.println( "Executing " + sql + " with: versionId[" + versionId + "] plantId[" + plantId + "] cornTypeId[" + cornTypeId + "]");
			
			rs = executeQuery(pstm);
			
			while (rs.next()){
				dto = new CostsPlants();
				
				dto.setPlantVersionId(rs.getInt("plant_version_id"));
				dto.setCornTypeId(rs.getInt("corn_type_id"));
				dto.setCornOriginId(rs.getString("corn_origin_id"));
				dto.setInitialCost(rs.getDouble("initial_cost"));
				dto.setInitialTons(rs.getDouble("initial_tons"));
				dto.setInitialUsd(rs.getDouble("initial_usd"));
				dto.setIncomingCost(rs.getDouble("incoming_cost"));
				dto.setIncomingTons(rs.getDouble("incoming_tons"));
				dto.setIncomingUsd(rs.getDouble("incoming_usd"));
				dto.setAvailableCost(rs.getDouble("available_cost"));
				dto.setAvailableTons(rs.getDouble("available_tons"));
				dto.setAvailableUsd(rs.getDouble("available_usd"));
				dto.setGrindCost(rs.getDouble("grind_cost"));
				dto.setGrindTons(rs.getDouble("grind_tons"));
				dto.setGrindUsd(rs.getDouble("grind_usd"));
				dto.setFinalCost(rs.getDouble("final_cost"));
				dto.setFinalTons(rs.getDouble("final_tons"));
				dto.setFinalUsd(rs.getDouble("final_usd"));
				
				array.add(dto);
			}
			
			array.trimToSize();
			
		}catch (PoolException poolException){
			Exception e = null;
			poolException.printStackTrace(System.out);
			e = new Exception(poolException);
			throw e;
		} catch(SQLException sqle){
			Exception e = null;
			sqle.printStackTrace(System.out);
			e = new Exception(sqle);
			throw e;
		}finally {
			try{ closeConnection(pstm.getConnection());	}catch(Exception exception){}
		}
		
		return array;
	}
}
